﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Data;

namespace ImportBLSData
{
    class Program
    {
        static void Main(string[] args)
        {
            string rootDirectory = "K:\\ftp.bls.gov\\time.series\\";
            DirectoryInfo rootDirInfo = new DirectoryInfo(rootDirectory);
            foreach (DirectoryInfo di in rootDirInfo.GetDirectories())
                ProcessDirectory(di);
            foreach (FileInfo fi in rootDirInfo.GetFiles())
                ProcessFile(fi);
        }

        private static void ProcessFile(FileInfo fi)
        {
            StreamReader sr = fi.OpenText();
            DataTable dt = new DataTable("temp");
            string line = sr.ReadLine();
            string[] Names = line.Split(new[] { "\t" }, StringSplitOptions.None);
            foreach (string s in Names)
                dt.Columns.Add(s, typeof(string));

            Server srv = new Server();
            Database db = srv.Databases["GNISCensusParcelMLS"];
            Table t = new Table(db, "temp", "dbo");
            string cols = string.Empty;
            foreach(string s in Names)
            {
                Column col = new Column(t, s, DataType.VarCharMax);
                t.Columns.Add(col);
                cols += s + ", ";
            }
            t.Create();
            cols = cols.Substring(0, cols.Length - 2);

            while ((line = sr.ReadLine()) != null)
            {
                DataRow dr = dt.NewRow();
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                for (int x = 0; x < t.Columns.Count; x++)
                {
                    dr[x] = values[x];
                }
                dt.Rows.Add(dr);
            }


            using (SqlConnection cn = new SqlConnection(srv.ConnectionContext.ConnectionString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(string.Empty, cn))
                {
                    string values = string.Empty;
                    foreach (DataRow dr in dt.Rows)
                    {
                        string cmdString = string.Format("insert into [{0}].[dbo].[{1}] ({2}) values (<values>)", db.Name, t.Name, cols);
                        values = string.Empty;
                        for (int x = 0; x < dt.Columns.Count; x++)
                            values += "'" + dr[x].ToString() + "', ";
                        values = values.Substring(0, values.Length - 2);
                        cmdString = cmdString.Replace("<values>", values);
                        cmd.CommandText = cmdString;
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            using (SqlConnection cn = new SqlConnection(srv.ConnectionContext.ConnectionString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(string.Empty, cn))
                {

                }
            }

            // switch (fi.Directory.Name)
            //{
            //    case "ap":
            //        //ProcessAPFile(fi);
            //        break;
            //    case "bd":
            //        ProcessBDFile(fi);
            //        break;
            //    case "bg":
            //        ProcessBGFile(fi);
            //        break;
            //    case "bp":
            //        ProcessBPFile(fi);
            //        break;
            //    case "cc":
            //        ProcessCCFile(fi);
            //        break;
            //    case "cd":
            //        ProcessCDFile(fi);
            //        break;
            //    case "ce":
            //        ProcessCEFile(fi);
            //        break;
            //    case "cf":
            //        ProcessCFFile(fi);
            //        break;
            //    case "ch":
            //        ProcessCHFile(fi);
            //        break;
            //    case "ci":
            //        ProcessCIFile(fi);
            //        break;
            //    case "cm":
            //        ProcessCMFile(fi);
            //        break;
            //    case "compressed":
            //        ProcessCompressedFile(fi);
            //        break;
            //    case "cs":
            //        ProcessCSFile(fi);
            //        break;
            //    case "cu":
            //        ProcessCUFile(fi);
            //        break;
            //    case "cw":
            //        ProcessCWFile(fi);
            //        break;
            //    case "cx":
            //        ProcessCXFile(fi);
            //        break;
            //    case "eb":
            //        ProcessEBFile(fi);
            //        break;
            //    case "ec":
            //        ProcessECFile(fi);
            //        break;
            //    case "ee":
            //        ProcessEEFile(fi);
            //        break;
            //    case "ei":
            //        ProcessEIFile(fi);
            //        break;
            //    case "fi":
            //        ProcessFIFile(fi);
            //        break;
            //    case "fm":
            //        ProcessFMFile(fi);
            //        break;
            //    case "fw":
            //        ProcessFWFile(fi);
            //        break;
            //    case "gg":
            //        ProcessGGFile(fi);
            //        break;
            //    case "gp":
            //        ProcessGPFile(fi);
            //        break;
            //    case "hc":
            //        ProcessHCFile(fi);
            //        break;
            //    case "hs":
            //        ProcessHSFile(fi);
            //        break;
            //    case "ii":
            //        ProcessIIFile(fi);
            //        break;
            //    case "in":
            //        ProcessINFile(fi);
            //        break;
            //    case "ip":
            //        ProcessIPFile(fi);
            //        break;
            //    case "jl":
            //        ProcessJLFile(fi);
            //        break;
            //    case "jt":
            //        ProcessJTFile(fi);
            //        break;
            //    case "la":
            //        ProcessLAFile(fi);
            //        break;
            //    case "le":
            //        ProcessLEFile(fi);
            //        break;
            //    case "li":
            //        ProcessLIFile(fi);
            //        break;
            //    case "ln":
            //        ProcessLNFile(fi);
            //        break;
            //    case "lu":
            //        ProcessLUFile(fi);
            //        break;
            //    case "ml":
            //        ProcessMLFile(fi);
            //        break;
            //    case "mp":
            //        ProcessMPFile(fi);
            //        break;
            //    case "mu":
            //        ProcessMUFile(fi);
            //        break;
            //    case "mw":
            //        ProcessMWFile(fi);
            //        break;
            //    case "nb":
            //        ProcessNBFile(fi);
            //        break;
            //    case "nc":
            //        ProcessNCFile(fi);
            //        break;
            //    case "nd":
            //        ProcessNDFile(fi);
            //        break;
            //    case "nl":
            //        ProcessNLFile(fi);
            //        break;
            //    case "nw":
            //        ProcessNWFile(fi);
            //        break;
            //    case "oe":
            //        ProcessOEFile(fi);
            //        break;
            //    case "pc":
            //        ProcessPCFile(fi);
            //        break;
            //    case "pd":
            //        ProcessPDFile(fi);
            //        break;
            //    case "pi":
            //        ProcessPIFile(fi);
            //        break;
            //    case "pr":
            //        ProcessPRFile(fi);
            //        break;
            //    case "sa":
            //        ProcessSAFile(fi);
            //        break;
            //    case "sh":
            //        ProcessSHFile(fi);
            //        break;
            //    case "si":
            //        ProcessSIFile(fi);
            //        break;
            //    case "sm":
            //        ProcessSMFile(fi);
            //        break;
            //    case "su":
            //        ProcessSUFile(fi);
            //        break;
            //    case "wd":
            //        ProcessWDFile(fi);
            //        break;
            //    case "wp":
            //        ProcessWPFile(fi);
            //        break;
            //    case "ws":
            //        ProcessWSFile(fi);
            //        break;
            //    case "xx":
            //        ProcessXXFile(fi);
            //        break;
            //    case "yy":
            //        ProcessYYFile(fi);
            //        break;
            //    default:
            //        break;
            //}
        }

        private static void ProcessYYFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessXXFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessWSFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessWPFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessWDFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessSUFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessSMFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessSIFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessSHFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessSAFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessPRFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessPIFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessPDFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessPCFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessOEFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessNWFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessNLFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessNDFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessNCFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessNBFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessMWFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessMUFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessMPFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessMLFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessLUFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessLNFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessLIFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessLEFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessLAFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessJTFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessJLFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessIPFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessINFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessIIFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessHSFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessHCFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessGPFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessGGFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessFWFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessFMFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessFIFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessEIFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessEEFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessECFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessEBFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCXFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCWFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCUFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCSFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCompressedFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCMFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCIFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCHFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCFFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCEFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCDFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessCCFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessBPFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessBGFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }
        private static void ProcessBDFile(FileInfo fi)
        {
            switch(fi.Extension)
            {
                case ".county":
                    ProcessBDCountyFile(fi);
                    break;
                case ".Current":
                    ProcessBDCurrentFile(fi);
                    break;
                case ".AllItems":
                    ProcessBDAllItemsFile(fi);
                    break;
                case ".dataclass":
                    ProcessBDDataClass(fi);
                    break;
                case ".dataelement":
                    ProcessBDDataElementFile(fi);
                    break;
                case ".footnote":
                    ProcessBDFootnoteFile(fi);
                    break;
                case ".industry":
                    ProcessBDIndustryFile(fi);
                    break;
                case ".msa":
                    ProcessBDMSAFile(fi);
                    break;
                case ".ownership":
                    ProcessBDOwnershipFile(fi);
                    break;
                case ".periodicity":
                    ProcessBDPeriodicity(fi);
                    break;
                case ".ratelevel":
                    ProcessBDRateLevelFile(fi);
                    break;
                case ".seasonal":
                    ProcessBDSeasonalFile(fi);
                    break;
                case ".series":
                    ProcessBDSeriesFile(fi);
                    break;
                case ".sizeclass":
                    ProcessBDSizeClassFile(fi);
                    break;
                case ".state":
                    ProcessBDStateFile(fi);
                    break;
                case ".unitanalysis":
                    ProcessBDUnitAnalysisFile(fi);
                    break;
                default:
                    break;
            }
        }

        private static void ProcessBDUnitAnalysisFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }

        private static void ProcessBDStateFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }

        private static void ProcessBDSizeClassFile(FileInfo fi)
        {
            throw new NotImplementedException();
        }

        private static void ProcessBDSeriesFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "SeriesID", DataType.NVarChar(30), false) { Nullable = true };
                Column colItemName = new Column(apItems, "Seasonal", DataType.NVarChar(1), false) { Nullable = true };
                Column colMSACode = new Column(apItems, "MSACode", DataType.NVarChar(5), false) { Nullable = true };
                Column colStateCode = new Column(apItems, "StateCode", DataType.NVarChar(2), false) { Nullable = true };
                Column colCountyCode = new Column(apItems, "CountyCode", DataType.NVarChar(3), false) { Nullable = true };
                Column colIndustryCode = new Column(apItems, "IndustryCode", DataType.NVarChar(6), false) { Nullable = true };
                Column colUnitAnalysisCode = new Column(apItems, "UnitAnalysisCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colDataElementCode = new Column(apItems, "DataElementCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colSizeClassCode = new Column(apItems, "SizeClassCode", DataType.NVarChar(2), false) { Nullable = true };
                Column colDataClassCode = new Column(apItems, "DataClassCode", DataType.NVarChar(2), false) { Nullable = true };
                Column colRateLevelCode = new Column(apItems, "RateLevelCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colPeriodicityCode = new Column(apItems, "PeriodicityCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colOwnershipCode = new Column(apItems, "OwnershipCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colFootnoteCodes = new Column(apItems, "FootnoteCodes", DataType.NVarChar(10), false) { Nullable = true };
                Column colBeginYear = new Column(apItems, "BeginYear", DataType.NVarChar(4), false) { Nullable = true };
                Column colBeginPeriod = new Column(apItems, "BeginPeriod", DataType.NVarChar(3), false) { Nullable = true };
                Column colEndYear = new Column(apItems, "EndYear", DataType.NVarChar(4), false) { Nullable = true };
                Column colEndPeriod = new Column(apItems, "EndPeriod", DataType.NVarChar(3), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where SeriesID = '{2}' and Seasonal = '{3}') and MSACode = '{4}' and StateCode = '{5}' and CountyCode = '{6}' ";
                cmd += "and IndustryCode = {7} and UnitAnalysisCode = '{8}' and DataElementCode = '{9}' and SizeClassCode = '{10}' and DataClassCode = '{11}' and RateLevelCode = '{12}' and ";
                cmd += "PeriodicityCode = '{13}' begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (SeriesID, Seasonal) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }

        private static void ProcessBDSeasonalFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "SeasonalCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colItemName = new Column(apItems, "SeasonalText", DataType.NVarChar(100), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where SeasonalCode = '{2}' and SeasonalText = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (SeasonalCode, SeasonalText) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }

        private static void ProcessBDRateLevelFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "RateLevelCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colItemName = new Column(apItems, "RateLevelName", DataType.NVarChar(30), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where RateLevelCode = '{2}' and RateLevelName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (RateLevelCode, RateLevelName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }

        private static void ProcessBDPeriodicity(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "PeriodicityCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colItemName = new Column(apItems, "PeriodicityName", DataType.NVarChar(30), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where PeriodicityCode = '{2}' and PeriodicityName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (PeriodicityCode, PeriodicityName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }

        private static void ProcessBDOwnershipFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "OwnershipCode", DataType.NVarChar(5), false) { Nullable = true };
                Column colItemName = new Column(apItems, "OwnershipName", DataType.NVarChar(100), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where OwnershipCode = '{2}' and OwnershipName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (OwnershipCode, OwnershipName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }

        private static void ProcessBDMSAFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "MSACode", DataType.NVarChar(5), false) { Nullable = true };
                Column colItemName = new Column(apItems, "MSAName", DataType.NVarChar(100), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where MSACode = '{2}' and MSAName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (MSACode, MSAName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessBDIndustryFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "IndustryCode", DataType.NVarChar(6), false) { Nullable = true };
                Column colItemName = new Column(apItems, "IndustryName", DataType.NVarChar(100), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where IndustryCode = '{2}' and IndustryName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (IndustryCode, IndustryName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessBDFootnoteFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "FootnoteCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colItemName = new Column(apItems, "FootnoteText", DataType.NVarChar(100), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where FootnoteCode = '{2}' and FootnoteText = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (FootnoteCode, FootnoteText) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessBDDataElementFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "DataElementCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colItemName = new Column(apItems, "DataElementName", DataType.NVarChar(80), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where DataElementCode = '{2}' and DataElementName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (DataElementCode, DataElementName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessBDDataClass(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "DataClassCode", DataType.NVarChar(2), false) { Nullable = true };
                Column colItemName = new Column(apItems, "DataClassName", DataType.NVarChar(60), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where DataClassCode = '{2}' and DataClassName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (DataClassCode, DataClassName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void LoadDataTable(Server svr, StringCollection sqlCommands)
        {
            Console.WriteLine("Loading data table");
            using (SqlConnection cn = new SqlConnection(svr.ConnectionContext.ConnectionString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(string.Empty, cn))
                {
                    foreach (string s in sqlCommands)
                    {
                        cmd.CommandText = s;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
        private static void ProcessBDAllItemsFile(FileInfo fi)
        {
            ProcessDataFile(fi);
        }
        private static void ProcessBDCurrentFile(FileInfo fi)
        {
            ProcessDataFile(fi);
        }
        private static void ProcessBDCountyFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "CountyCode", DataType.NVarChar(3), false) { Nullable = true };
                Column colItemName = new Column(apItems, "CountyName", DataType.NVarChar(100), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where CountyCode = '{2}' and CountyName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (CountyCode, CountyName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }

            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessAPFile(FileInfo fi)
        {
            switch(fi.Extension)
            {
                case ".area":
                    ImportBLSData.ap.Area.ProcessAPAreaFile(fi);
                    break;
                case ".Current":
                    ProcessAPCurrentFile(fi);
                    break;
                case ".HouseholdFuels":
                    ProcessAPHouseholdFuelsFile(fi);
                    break;
                case ".Gasoline":
                    ProcessAPGasolineFile(fi);
                    break;
                case ".Food":
                    ProcessAPFoodFile(fi);
                    break;
                case ".footnote":
                    ProcessAPFootNoteFile(fi);
                    break;
                case ".item":
                    ProcessAPItemFile(fi);
                    break;
                case ".period":
                    ProcessAPPeriodFile(fi);
                    break;
                case ".series":
                    ProcessAPSeriesFile(fi);
                    break;
                default:
                    break;
            }
        }
        private static void ProcessAPSeriesFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colSeriesID = new Column(apItems, "SeriesID", DataType.NVarChar(17), false) { Nullable = true };
                Column colAreaCode = new Column(apItems, "AreaCode", DataType.NVarChar(4), false) { Nullable = true };
                Column colItemCode = new Column(apItems, "ItemCode", DataType.NVarChar(7), false) { Nullable = true };
                Column colFootnoteCodes = new Column(apItems, "FootnoteCodes", DataType.NVarCharMax, false) { Nullable = true };
                Column colBeginYear = new Column(apItems, "BeginYear", DataType.NVarChar(4), false) { Nullable = true };
                Column colBeginPeriod = new Column(apItems, "BeginPeriod", DataType.NVarChar(3), false) { Nullable = true };
                Column colEndYear = new Column(apItems, "EndYear", DataType.NVarChar(4), false) { Nullable = true };
                Column colEndPeriod = new Column(apItems, "EndPeriod", DataType.NVarChar(3), false) { Nullable = true };
                apItems.Columns.Add(colSeriesID);
                apItems.Columns.Add(colAreaCode);
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colFootnoteCodes);
                apItems.Columns.Add(colBeginYear);
                apItems.Columns.Add(colBeginPeriod);
                apItems.Columns.Add(colEndYear);
                apItems.Columns.Add(colEndPeriod);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where SeriesID = '{2}' and AreaCode = '{3}' and ItemCode = '{4}' and BeginYear = '{5}' and BeginPeriod = '{6}' and EndYear = '{7}' and EndPeriod = '{8}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (SeriesID, AreaCode, ItemCode, BeginYear, BeginPeriod, EndYear, EndPeriod, FootnoteCodes) values ('{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim(), values[2].Trim(), values[4].Trim(), values[5].Trim(), values[6].Trim(), values[7].Trim(), values[3].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessAPPeriodFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colPeriod = new Column(apItems, "Period", DataType.NVarChar(3), false) { Nullable = true };
                Column colPeriodAbbr = new Column(apItems, "PeriodAbbr", DataType.NVarChar(5), false) { Nullable = true };
                Column colPeriodName = new Column(apItems, "PeriodName", DataType.NVarChar(20), false) { Nullable = true };
                apItems.Columns.Add(colPeriod);
                apItems.Columns.Add(colPeriodAbbr);
                apItems.Columns.Add(colPeriodName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where Period = '{2}' and PeriodAbbr = '{3}' and PeriodName = '{4}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (Period, PeriodAbbr, PeriodName) values ('{2}', '{3}', '{4}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim(), values[2].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessAPItemFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apItems = new Table(dbGNIS, fi.Name);
                Column colItemCode = new Column(apItems, "ItemCode", DataType.NVarChar(7), false) { Nullable = true };
                Column colItemName = new Column(apItems, "ItemName", DataType.NVarChar(100), false) { Nullable = true };
                apItems.Columns.Add(colItemCode);
                apItems.Columns.Add(colItemName);
                apItems.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where ItemCode = '{2}' and ItemName = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (ItemCode, ItemName) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessAPFootNoteFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apFootnotes = new Table(dbGNIS, fi.Name);
                Column colFootnoteCode = new Column(apFootnotes, "FootnoteCode", DataType.NVarChar(1), false) { Nullable = true };
                Column colFootnoteText = new Column(apFootnotes, "FootnoteText", DataType.NVarChar(100), false) { Nullable = true };
                apFootnotes.Columns.Add(colFootnoteCode);
                apFootnotes.Columns.Add(colFootnoteText);
                apFootnotes.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where FootnoteCode = '{2}' and FootnoteText = '{3}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (FootnoteCode, FootnoteText) values ('{2}', '{3}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }
        private static void ProcessAPFoodFile(FileInfo fi)
        {
            ProcessDataFile(fi);
        }
        private static void ProcessAPGasolineFile(FileInfo fi)
        {
            ProcessDataFile(fi);
        }
        private static void ProcessAPHouseholdFuelsFile(FileInfo fi)
        {
            ProcessDataFile(fi);
        }
        private static void ProcessAPCurrentFile(FileInfo fi)
        {
            ProcessDataFile(fi);
        }
        private static void ProcessDataFile(FileInfo fi)
        {
            Console.WriteLine("Working on " + fi.Name);

            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apData0Current = new Table(dbGNIS, fi.Name);
                Column colSeriesID = new Column(apData0Current, "SeriesID", DataType.NVarChar(17), false) { Nullable = true };
                Column colYear = new Column(apData0Current, "Year", DataType.NVarChar(4), false) { Nullable = true };
                Column colPeriod = new Column(apData0Current, "Period", DataType.NVarChar(3), false) { Nullable = true };
                Column colValue = new Column(apData0Current, "Value", DataType.NVarChar(12), false) { Nullable = true };
                Column colFootnoteCodes = new Column(apData0Current, "FootnoteCodes", DataType.NVarChar(10), false) { Nullable = true };
                apData0Current.Columns.Add(colSeriesID);
                apData0Current.Columns.Add(colYear);
                apData0Current.Columns.Add(colPeriod);
                apData0Current.Columns.Add(colValue);
                apData0Current.Columns.Add(colFootnoteCodes);
                apData0Current.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            Console.WriteLine("Loading sqlCommands");
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                string cmd = "if not exists (select 1 from [{0}].[dbo].[{1}] where SeriesID = '{2}' and [Year] = '{3}' and Period = '{4}' and [Value] = '{5}') begin ";
                cmd += "insert into [{0}].[dbo].[{1}] (SeriesID, [Year], [Period], [Value], [FootnoteCodes]) values ('{2}', '{3}', '{4}', '{5}', '{6}') end";
                string cmd2 = string.Format(cmd, dbGNIS.Name, fi.Name, values[0].Trim(), values[1].Trim(), values[2].Trim(), values[3].Trim(), values[4].Trim());
                sqlCommands.Add(cmd2);
            }
            LoadDataTable(svr, sqlCommands);
        }

        private static void ProcessDirectory(DirectoryInfo di)
        {
            foreach (DirectoryInfo dix in di.GetDirectories())
                ProcessDirectory(dix);
            foreach (FileInfo fix in di.GetFiles())
                ProcessFile(fix);
        }
    }
}
